Relational databases may be defined (source: Wikipedia) as a database that has a collection of tables of data items, all of which are formally described and organized according to the relational model. The term—relational database—is in contrast to only one table as the database, and in contrast to other models which also have many tables in a single database.
In the relational model, each table schema should identify a primary column, called the primary key, used for identifying a row. Tables can relate by using a foreign key that usually points to the primary key of another table. The relational model offers various levels of refinement of the table relations called database normalization. (See Normalization below). The database management system (DBMS) of a relational database is called a relational database management system (RDBMS), and is the software of a relational database.
When creating a database, usually the designer uses separate tables for different types of entities, for example: users, employees, customers, orders, items etc. What is further needed are relationships between these tables. For instance, customers make orders, and orders contain items. These relationships need to be represented in the database.
There are several types of database relationships, such as: One-to-One Relationships, One-to-Many and Many-to-One Relationships, Many-to-Many Relationships, and Self Referencing Relationships.
These relationships have to be hardcoded by the database designer, which is quite limiting in large databases, where very high number of relations between data structures may exist. Each change in relationships defined in the database required deep interference with database schema and database processes. Therefore, it cannot be executed by a regular end user of a database system.
Modifications in complex database schemas are very demanding as they need a lot of tests, field trials, and experienced database engineers to design and implement.
Structured Query Language (SQL) provides for creation of Views. A View may be compared to a virtual table, through which a section of data from one or more tables may be viewed. Views however, do not contain data of their own. A View is stored as a SELECT statement in the database. A View contains rows and columns, just like a real table. The fields in a View are fields from one or more real tables in the database.
Therefore, in order to create ad-hoc relationships between objects of different tables, one may create a View and store such view in order to make it available for any other user of the database system.
Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table database query then simple queries on the views may take considerable time. This results in significant performance decrease.
Additionally, views are new database objects, which means they occupy space and operational memory.
RDBMS often provides a capability of stored procedures. Stored procedures allow coupling set-based power of SQL with iterative and conditional processing control of code development.
Drawbacks of stored procedures include limited portability (especially when moving from one database type to another—sometimes even between different versions of the same database), any data errors in handling stored procedures are not generated until runtime. Additionally, too much overhead is incurred from maintaining Stored Procedures that are not complex enough. As a result, simple SELECT statements should not be executed as stored procedures.
With SQL, there is a possibility to copy information from one table into another. The SELECT INTO statement copies data from one table and inserts it into a new table. This creates a new physical table (PT) that represents data of other tables at the moment of creation of the new table (PT). If after executing SELECT INTO there are created new relations, the new table (PT) will not reflect it.
As such, there is a need to design and implement an efficient storing of ad hoc relations between previously unrelated database objects assigned to different database structures